﻿using System;
using System.Collections;
using Huirui.Cavan.Domain.MainBoundedContext.SearchModule;
using Huirui.Cavan.Domain.MainBoundedContext.PurchaseModule;
using Huirui.Cavan.Data.Seedwork;
using Huirui.Cavan.Domain.MainBoundedContext.ReportModule;
using System.Collections.Generic;
using System.Text;
using Huirui.Cavan.Core.Extension;
using NHibernate;
using Spring.Data.Common;
using System.Data;

namespace Huirui.Cavan.Infrastructure.Data.MainBoundedContext.PurchaseModule
{
	#region PurchaseReturn

    public class PurchaseReturnRepository : Repository<PurchaseReturn, PurchaseReturnSearch>, IPurchaseReturnRepository
	{
        public IList<PurchaseReturnReport> GetPurchaseReturnReport(DateTime? startDate, DateTime? endDate, string purchaseReturnCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT pr.Code AS PurchaseReturnCode, pr.ReturnDate, pr.SupplierCode, s.Name AS SupplierName, pr.Currency, ");
            sql.Append("pr.CurrencyRate, t.Amount, pr.CheckedStatus, ISNULL(pr.SupplierInvoiceNumber, '') AS SupplierInvoiceNumber, ISNULL(pr.PurchaseEmployeeCode, '') AS PurchaseEmployeeCode, ISNULL(ua.EmployeeCode, '') AS EmployeeCode, ISNULL(pr.Remark, '') AS Remark ");
            sql.Append("FROM PurchaseReturn pr WITH (NOLOCK) INNER JOIN ( ");
            sql.Append("SELECT prd.PurchaseReturnCode, SUM(prd.Number * prd.UnitCost) AS Amount ");
            sql.Append("FROM PurchaseReturnDetail prd WITH(NOLOCK) ");
            sql.Append("GROUP BY prd.PurchaseReturnCode) AS t ON pr.Code = t.PurchaseReturnCode ");
            sql.Append("INNER JOIN Supplier s WITH (NOLOCK) ON s.Code = pr.SupplierCode ");
            sql.Append("INNER JOIN UserAccount ua WITH (NOLOCK) ON pr.EditUser = ua.ID ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  pr.CreateDate >= @StartDate AND pr.CreateDate <= @EndDate ");
            }
            if (purchaseReturnCode.IsNotEmpty())
            {
                sql.Append("AND pr.Code = @PurchaseReturnCode ");
            }

            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (purchaseReturnCode.IsNotEmpty())
            {
                paras.Add("PurchaseReturnCode", DbType.String).Value = purchaseReturnCode;
            }

            return this.AdoTemplate.QueryWithRowMapper<PurchaseReturnReport>(CommandType.Text, sql.ToString(), new PurchaseReturnReportMapper(), paras);
        }
	}
	#endregion
}